CREATE OR REPLACE FUNCTION sza_oie_create_amico(c_001 varchar2)
  RETURN NUMBER AUTHID CURRENT_USER
  IS
  /* $Header: ARTAESDY.pls 115.3 2000/07/10 16:18:52 pkm ship      $ */
BEGIN

  declare

    cursor amicos_oie is
      select svm.vendor_num a1, to_char(svm.o_vendor_site_id) a2
        from sza_vendor_mapping svm
       where svm.source_code = 'AMICOS-ZH'
         and svm.o_vendor_site_id is not null
       order by 2;

    l_sum_desc    varchar2(200);
    l_sum_number  varchar2(200);
    l_last_number varchar2(200);

  begin

    delete from sza_vendor_mapping_oie;
    for v_ami in amicos_oie loop

      declare

        cursor amicos_oie_child is

          select svm1.vendor_num vendor_num,
                 to_char(svm1.o_vendor_site_id) o_vendor_site_id
            from sza_vendor_mapping svm1
           where svm1.o_vendor_site_id = v_ami.a2
           order by 2;

      begin

        l_sum_desc   := null;
        l_sum_number := null;

        for v_ami_01 in amicos_oie_child loop

          l_sum_desc   := l_sum_desc || v_ami_01.vendor_num || ',';
          l_sum_number := v_ami_01.o_vendor_site_id;

        end loop;
        if l_sum_number <> nvl(l_last_number,'@@@') then
          insert into sza_vendor_mapping_oie soie
            (soie.vendor_num, soie.o_vendor_site_id)
          values
            ( substr(l_sum_desc, 1, length(l_sum_desc) - 1),l_sum_number);
        end if;

        l_last_number := l_sum_number;
      end;

    end loop;

  end;

  RETURN(0);
END sza_oie_create_amico;
/
